package com.abss.vote.util;
import com.abss.vote.model.DistrictListUserWorkVo;
import com.abss.vote.model.MpInfo;
import com.abss.vote.model.UserWorkVo;
import org.apache.poi.hssf.usermodel.*;
import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.List;

public class ExcelUtil {

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //创建内容
        for(int i=0;i<values.length;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }

    /**
     *
     * @param sheetName sheet名称
     * @param title 标题
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbooks(String sheetName, String []title, List<UserWorkVo> list, HSSFWorkbook wb, MpInfo mp){
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        BufferedImage bufferImg = null;//图片一
        try {
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for(int i=0;i<list.size();i++){
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                UserWorkVo userWorkVo = list.get(i);
                //将内容按顺序赋给对应的列对象
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

                //将图片下载到本服务器上
                String WORK_IMGURL = userWorkVo.getWORK_IMGURL();
                String path = mp.getFILE_PATH() + "copy/1.jpg";
                Download.downloadPicture(WORK_IMGURL, path);
                if (new File(path).exists()) {
                    bufferImg = ImageIO.read(new File(path));
                    ImageIO.write(bufferImg, "jpg", byteArrayOut);
                    //图片一导出到单元格B2中
                    HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
                            (short) 1, i+1, (short) 1, i+1);
                    // 插入图片,注意这个问题PICTURE_TYPE_JPEG。如果导出的图片没有显示出来，改动这里
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                }
                Download.deleteFile(new File(path));
                cell = row.createCell(0);cell.setCellValue(userWorkVo.getWORK_ID());//作品编号
                cell.setCellStyle(styleCon);
                cell = row.createCell(2);cell.setCellValue(userWorkVo.getWORK_NAME());//作品名称
                cell.setCellStyle(styleCon);
                cell = row.createCell(3);cell.setCellValue(userWorkVo.getUSER_NAME());//作者
                cell.setCellStyle(styleCon);
                cell = row.createCell(4);cell.setCellValue(userWorkVo.getAGE());//年龄
                cell.setCellStyle(styleCon);
                cell = row.createCell(5);cell.setCellValue(userWorkVo.getTELEPHONE());//联系方式
                cell.setCellStyle(styleCon);
                cell = row.createCell(6);cell.setCellValue(userWorkVo.getDISTRICT_NAME());//推荐单位
                cell.setCellStyle(styleCon);
                String s = "";
                if(userWorkVo.getAUDIT_STATE()==0){
                    s="未审核";
                }else if(userWorkVo.getAUDIT_STATE()==1){
                    s="审核通过";
                }else if(userWorkVo.getAUDIT_STATE()==2){
                    s="审核不通过";
                }
                cell = row.createCell(7);cell.setCellValue(s);//审核状态
                cell.setCellStyle(styleCon);
                String t = "";
                if("0".equals(userWorkVo.getWORK_TYPE())){
                    t="视频";
                }else if("1".equals(userWorkVo.getWORK_TYPE())){
                    t="音频";
                }else if("2".equals(userWorkVo.getWORK_TYPE())){
                    t="图片";
                }
                cell = row.createCell(8);cell.setCellValue(t);//作品类型
                cell.setCellStyle(styleCon);
                cell = row.createCell(9);cell.setCellValue(userWorkVo.getVOTES_NUMBER());//获得票数
                cell.setCellStyle(styleCon);

                cell = row.createCell(10);cell.setCellValue(userWorkVo.getWORK_VISIST());//获得票数
                cell.setCellStyle(styleCon)

                ;
                cell = row.createCell(11);cell.setCellValue(userWorkVo.getPRIZE_NAME());//奖项信息
                cell.setCellStyle(styleCon);
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cell = row.createCell(12);cell.setCellValue(formatter.format(userWorkVo.getWORK_UPTIME()));//上传时间
                cell.setCellStyle(styleCon);
                cell = row.createCell(13);cell.setCellValue(userWorkVo.getWORK_ASSESSMENT());//作品描述
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            // TODO: handle exception
            System.err.println(e.getMessage());
        }
        return wb;
    }

    /**
     *
     * @param sheetName sheet名称
     * @param title 标题
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getSignInfoHSSFWorkbook(String sheetName, String[] title,
            List<DistrictListUserWorkVo> list, HSSFWorkbook wb, MpInfo mp) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            //创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                DistrictListUserWorkVo userInfo = list.get(i);

                cell = row.createCell(0);
                cell.setCellValue(userInfo.getUSER_ID());
                cell.setCellStyle(styleCon);

                cell = row.createCell(1);
                cell.setCellValue(userInfo.getUSER_NAME());
                cell.setCellStyle(styleCon);

                cell = row.createCell(2);
                if (null == userInfo.getGENDER()) {
                    cell.setCellValue("未知");
                } else {
                    if (1 == userInfo.getGENDER()) {
                        cell.setCellValue("男");
                    } else if (2 == userInfo.getGENDER()) {
                        cell.setCellValue("女");
                    } else {
                        cell.setCellValue("未知");
                    }
                }
                cell.setCellStyle(styleCon);

                cell = row.createCell(3);
                cell.setCellValue(userInfo.getBIRTHDAY());
                cell.setCellStyle(styleCon);

                cell = row.createCell(4);
                cell.setCellValue(userInfo.getAGE());
                cell.setCellStyle(styleCon);

                cell = row.createCell(5);
                cell.setCellValue(userInfo.getCITY());
                cell.setCellStyle(styleCon);

                cell = row.createCell(6);
                cell.setCellValue(userInfo.getDISTRICT_NAME());//作者
                cell.setCellStyle(styleCon);

                cell = row.createCell(7);
                cell.setCellValue(userInfo.getTELEPHONE());//获得票数
                cell.setCellStyle(styleCon);

                cell = row.createCell(8);
                cell.setCellValue(formatter.format(userInfo.getSIGN_TIME()));//上传时间
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getMessage());
        }
        return wb;
    }
    
    /**
     *
     * @param sheetName sheet名称
     * @param title     标题
     * @param wb        HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getWorkDeatailHSSFWorkbook(String sheetName, String[] title,
            List<DistrictListUserWorkVo> list, HSSFWorkbook wb, MpInfo mp) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 声明列对象
        HSSFCell cell = null;

        // 创建标题
        for (int i = 0; i < title.length; i++) {
            sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);// 设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 创建内容
            HSSFCellStyle styleCon = wb.createCellStyle();
            styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
            styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for (int i = 0; i < list.size(); i++) {
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                DistrictListUserWorkVo userInfo = list.get(i);

                cell = row.createCell(0);
                cell.setCellValue(userInfo.getUSER_ID());
                cell.setCellStyle(styleCon);

                cell = row.createCell(1);
                cell.setCellValue(userInfo.getUSER_NAME());
                cell.setCellStyle(styleCon);

                cell = row.createCell(2);
                if (null == userInfo.getGENDER()) {
                    cell.setCellValue("未知");
                } else {
                    if (1 == userInfo.getGENDER()) {
                        cell.setCellValue("男");
                    } else if (2 == userInfo.getGENDER()) {
                        cell.setCellValue("女");
                    } else {
                        cell.setCellValue("未知");
                    }
                }
                cell.setCellStyle(styleCon);

                cell = row.createCell(3);
                cell.setCellValue(userInfo.getBIRTHDAY());
                cell.setCellStyle(styleCon);

                cell = row.createCell(4);
                cell.setCellValue(userInfo.getAGE());
                cell.setCellStyle(styleCon);

                cell = row.createCell(5);
                cell.setCellValue(userInfo.getCITY());
                cell.setCellStyle(styleCon);

                cell = row.createCell(6);
                cell.setCellValue(userInfo.getDISTRICT_NAME());// 作者
                cell.setCellStyle(styleCon);

                cell = row.createCell(7);
                cell.setCellValue(userInfo.getTELEPHONE());// 获得票数
                cell.setCellStyle(styleCon);

                cell = row.createCell(8);
                cell.setCellValue(formatter.format(userInfo.getSIGN_TIME()));// 上传时间
                cell.setCellStyle(styleCon);

                cell = row.createCell(9);
                cell.setCellValue(userInfo.getTEST_TOTAL());
                cell.setCellStyle(styleCon);

                cell = row.createCell(10);
                try {
                    cell.setCellValue(userInfo.getTEST_LEVEL());
                } catch (Exception e) {
                    cell.setCellValue("");
                }
                cell.setCellStyle(styleCon);

                cell = row.createCell(11);
                try {
                    cell.setCellValue(userInfo.getTEST_SCORE());
                } catch (Exception e) {
                    cell.setCellValue("");
                }
                cell.setCellStyle(styleCon);

                cell = row.createCell(12);
                try {
                    cell.setCellValue(userInfo.getTEST_QUIZ_TIME());
                } catch (Exception e) {
                    cell.setCellValue("");
                }
                cell.setCellStyle(styleCon);

                cell = row.createCell(13);
                cell.setCellValue(getStatus(userInfo.getCUR_STATUS()));
                cell.setCellStyle(styleCon);
            }
            return wb;
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println(e.getMessage());
        }
        return wb;
    }

    private static String getStatus(Integer status) {
        switch (status) {
            case 0:
                return "报名成功";
            case 1:
                return "初赛完成";
            case 2:
                return "晋级复赛";
            case 3:
                return "复赛完成";
            case 4:
                return "晋级决赛";
            case 5:
                return "决赛完成";
            case 6:
                return "结束";
            default:
                return "未知";
        }
    }

}
